/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package schooldb;

/**
 *
 * @author ragdeep, camaria, fatima
 */

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DatabaseHandler {
 
    public static void main(String args[]) {
    DatabaseHandler dbhandler = new DatabaseHandler();
    ResultSet rs = dbhandler.executeQueryAndReturnResultSet("select * from student");
    
        try {
            while( rs.next() ){
                
                String studentID = rs.getString("studentID");
                String lastName = rs.getString("lastName");
                System.out.println(studentID + " " + lastName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    }
    
    // singleton shared instance
    static DatabaseHandler sharedInstance;
    // connection settings
    final String url = "";
    final String username = "";
    final String password = "";
    Connection conn;
    private Statement stmt;
    private DatabaseHandler() {
           // setup connection
        connectionSetup();
    }

    protected void connectionSetup() {
        try {
            // create an instance of the JDBC driver from the class file
            try {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            } catch (Exception e) {
                System.out.println("Could not load JDBC driver.");
                System.exit(1);
            }
            //create connection
            conn = DriverManager.getConnection(url, username, password);
            if (conn == null) {
                throw new SQLException("Database connction fail.");
            }
            System.out.println("Database connection established");
        } catch (SQLException e) {
            System.err.println(e);
        } 
    }
    public Boolean executeUpdate( String query ) {
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException ex) {
            if( ex.getMessage().contains("league_dev.duplicate_error") ) {
                System.out.println("duplicate");
                return false;
            }
            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
        return true;
    }

    public ResultSet executeQueryAndReturnResultSet(String query) {
        ResultSet result = null;
        try {
            Statement s = conn.createStatement();
            s.executeQuery(query);
            result = s.getResultSet();
            //result.close();
            //s.close();
        } catch( SQLException e ) {
            System.out.println("Error executing " + query + " : " + e);
        }
        return result;
    }

    public static DatabaseHandler getInstance() {
        if (sharedInstance == null) {
            sharedInstance = new DatabaseHandler();
        }
        return sharedInstance;
    }
    
    @Override
    public void finalize() {
        if (conn != null) {
            try {
                conn.close();
                System.out.println("Database connection terminated");
            } catch (Exception e) {
                System.out.println(e);
            }
        }
        try {
            super.finalize();
        } catch (Throwable ex) {
            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
